#! /bin/bash


DT=`date -d '0 days ago' "+%Y-%m-%d"`

if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

echo '========================================'
echo '==============开始增量导入==============='
echo '========================================'


export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

CREATE TABLE course_table_upload_detail(
           id  INT  COMMENT 'id',
           base_id  INT    COMMENT '课程主表id',
           class_id  INT    COMMENT '班级id',
           class_date  date    COMMENT '上课日期',
           content  string        COMMENT '课程内容',
           teacher_id  INT    COMMENT '老师id',
           teacher_name  string        COMMENT '老师名字',
           job_number  string        COMMENT '工号',
           classroom_id  INT    COMMENT '教室id',
           classroom_name  string        COMMENT '教室名称',
           is_outline  INT    COMMENT '是否大纲 0 否 1 是',
           class_mode  INT    COMMENT '上课模式 0 传统全天 1 AB上午 2 AB下午 3 线上直播',
           is_stage_exam  INT    COMMENT '是否阶段考试（0：否 1：是）',
           is_pay  INT    COMMENT '代课费（0：无 1：有）',
           tutor_teacher_id  INT    COMMENT '晚自习辅导老师id',
           tutor_teacher_name  string        COMMENT '辅导老师姓名',
           tutor_job_number  string        COMMENT '晚自习辅导老师工号',
           is_subsidy  INT    COMMENT '晚自习补贴（0：无 1：有）',
           answer_teacher_id  INT    COMMENT '答疑老师id',
           answer_teacher_name  string        COMMENT '答疑老师姓名',
           answer_job_number string        COMMENT '答疑老师工号',
           remark string        COMMENT '备注',
           create_time  string  COMMENT '创建时间')
    COMMENT'班级排课信息表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress'='ZLIB');


DROP TABLE if exists tbh_class_time_table;
CREATE TABLE tbh_class_time_table (
          id   INT  COMMENT '主键id',
          class_id   INT   COMMENT '班级id',
          morning_template_id   INT   COMMENT '上午出勤模板id',
          morning_begin_time   string   COMMENT '上午开始时间',
          morning_end_time   string   COMMENT '上午结束时间',
          afternoon_template_id   INT   COMMENT '下午出勤模板id',
          afternoon_begin_time  string  COMMENT '下午开始时间',
          afternoon_end_time   string  COMMENT '下午结束时间',
          evening_template_id   INT   COMMENT '晚上出勤模板id',
          evening_begin_time   string   COMMENT '晚上开始时间',
          evening_end_time   string   COMMENT '晚上结束时间',
          use_begin_date   date   COMMENT '使用开始日期',
          use_end_date   date   COMMENT '使用结束日期',
          create_time   string  COMMENT '创建时间',
          create_person   INT   COMMENT '创建人',
          remark   string COMMENT '备注')
    COMMENT'班级作息时间表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress'='ZLIB');


"
#SQOOP_HOME=/opt/cloudera/parcels/CDH-6.2.1-1.cdh6.2.1.p0.1425774/bin/sqoop
export LANG=zh_CN.UTF-8
SQOOP_HOME=/usr/bin/sqoop


# 全量
echo '--------------tbh_class_time_table--------------'
sqoop import \
--connect jdbc:mysql://hadoop01:3306/teach \
--username root \
--password 123456 \
--query "select id, class_id, morning_template_id, morning_begin_time, morning_end_time, afternoon_template_id, afternoon_begin_time, afternoon_end_time, evening_template_id, evening_begin_time, evening_end_time, use_begin_date, use_end_date, create_time, create_person, remark from teach.tbh_class_time_table where \$CONDITIONS" \
--hcatalog-database db_ods \
--hcatalog-table tbh_class_time_table \
--fields-terminated-by  '\t' \
-m 1

wait


echo '--------------course_table_upload_detail--------------'
sqoop import \
--connect jdbc:mysql://hadoop01:3306/teach \
--username root \
--password 123456 \
--query "select id, base_id, class_id, class_date, content, teacher_id, teacher_name, job_number, classroom_id, classroom_name, is_outline, class_mode, is_stage_exam, is_pay, tutor_teacher_id, tutor_teacher_name, tutor_job_number, is_subsidy, answer_teacher_id, answer_teacher_name, answer_job_number, remark, create_time from teach.course_table_upload_detail where \$CONDITIONS" \
--hcatalog-database db_ods \
--hcatalog-table course_table_upload_detail \
--fields-terminated-by  '\t' \
-m 1
wait


# 增量

echo '--------------class_studying_student_count--------------'
sqoop import \
--connect jdbc:mysql://hadoop01:3306/teach \
--username root \
--password 123456 \
--query "select id, school_id, subject_id, class_id, studying_student_count, studying_date, '${DT}' as dt from teach.class_studying_student_count where studying_date='${DT}' and\$CONDITIONS" \
--hcatalog-database db_ods \
--hcatalog-table class_studying_student_count \
--fields-terminated-by  '\t' \
-m 1
wait

echo '--------------student_leave_apply--------------'
sqoop import \
--connect jdbc:mysql://hadoop01:3306/teach \
--username root \
--password 123456 \
--query "select id, class_id, student_id, audit_state, audit_person, audit_time, audit_remark, leave_type, leave_reason, begin_time, begin_time_type, end_time, end_time_type, days, cancel_state, cancel_time, old_leave_id, leave_remark, valid_state, create_time, '${DT}' as dt from teach.student_leave_apply where date_format(create_time,'%Y-%m-%d')='${DT}' and \$CONDITIONS" \
--hcatalog-database db_ods \
--hcatalog-table student_leave_apply \
--fields-terminated-by  '\t' \
-m 1
wait

echo '--------------tbh_student_signin_record--------------'
sqoop import \
--connect jdbc:mysql://hadoop01:3306/teach \
--username root \
--password 123456 \
--query "select id, normal_class_flag, time_table_id, class_id, student_id, signin_time, signin_date, inner_flag, signin_type, share_state, inner_ip, '${DT}' as dt from teach.tbh_student_signin_record where signin_date='${DT}'  and \$CONDITIONS" \
--hcatalog-database db_ods \
--hcatalog-table tbh_student_signin_record \
--fields-terminated-by  '\t' \
-m 1
wait

echo '========================================'
echo '=================success==============='
echo '========================================'